﻿namespace Learning.Models
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Transactions;
    using System.Web;

    public class FileRepository
    {
        public FileModel Create(FileModel model)
        {
            var id = Guid.NewGuid();
            var time = DateTime.UtcNow;

            return Execute(command =>
            {
                command.CommandText = "INSERT tbl_file (uid_id, vb_data, vc_content_type, nvc_file_name, dt_created, dt_modified) VALUES (@uid_id, @vb_data, @vc_content_type, @nvc_file_name, @dt_created, @dt_modified)";
                AddParameter(command, "@uid_id", DbType.Guid, 0, id);
                AddParameter(command, "@vb_data", DbType.Binary, 0, model.Binary);
                AddParameter(command, "@vc_content_type", DbType.String, 128, model.ContentType);
                AddParameter(command, "@nvc_file_name", DbType.String, 128, model.FileName);
                AddParameter(command, "@dt_created", DbType.DateTime, 0, time);
                AddParameter(command, "@dt_modified", DbType.DateTime, 0, time);

                if (command.ExecuteNonQuery() != 0)
                {
                    model.Id = id;
                    model.Created = time;
                    model.Modified = time;

                    return model;
                }

                return null;
            });
        }

        public bool Update(FileModel model)
        {
            var time = DateTime.UtcNow;

            return Execute(command =>
            {
                command.CommandText = "UPDATE tbl_file SET vb_data = @vb_data, vc_content_type = @vc_content_type, nvc_file_name = @nvc_file_name, dt_modified = @dt_modified WHERE uid_id = @uid_id";
                AddParameter(command, "@uid_id", DbType.Guid, 0, model.Id);
                AddParameter(command, "@vb_data", DbType.Binary, 0, model.Binary);
                AddParameter(command, "@vc_content_type", DbType.String, 128, model.ContentType);
                AddParameter(command, "@nvc_file_name", DbType.String, 128, model.FileName);
                AddParameter(command, "@dt_modified", DbType.DateTime, 0, time);

                return command.ExecuteNonQuery() != 0;
            });
        }

        public bool Delete(Guid id)
        {
            return Execute(command =>
            {
                command.CommandText = "DELETE tbl_file WHERE uid_id = @uid_id";
                AddParameter(command, "@uid_id", DbType.Guid, 0, id);

                return command.ExecuteNonQuery() != 0;
            });
        }

        public FileModel Select(Guid id)
        {
            return Execute(command =>
            {
                command.CommandText = "SELECT vb_data, vc_content_type, nvc_file_name, dt_created, dt_modified FROM tbl_file WHERE uid_id = @uid_id";
                AddParameter(command, "@uid_id", DbType.Guid, 0, id);

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new FileModel()
                        {
                            Id = id,
                            Binary = GetBytes(reader, 0),
                            ContentType = reader.GetString(1),
                            FileName = reader.GetString(2),
                            Created = reader.GetDateTime(3),
                            Modified = reader.GetDateTime(4)
                        };
                    }
                }

                return null;
            });
        }

        public IEnumerable<FileModel> Select()
        {
            return Execute(command =>
            {
                var models = new List<FileModel>();

                command.CommandText = "SELECT uid_id, vb_data, vc_content_type, nvc_file_name, dt_created, dt_modified FROM tbl_file";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        models.Add(new FileModel()
                        {
                            Id = reader.GetGuid(0),
                            Binary = GetBytes(reader, 1),
                            ContentType = reader.GetString(2),
                            FileName = reader.GetString(3),
                            Created = reader.GetDateTime(4),
                            Modified = reader.GetDateTime(5)
                        });
                    }
                }

                return models;
            });
        }

        private static byte[] GetBytes(DbDataReader reader, int ordinal)
        {
            var result = new byte[reader.GetBytes(ordinal, 0, null, 0, 0)];

            using (var stream = new MemoryStream())
            {
                using (var writer = new BinaryWriter(stream, Encoding.ASCII, true))
                {
                    var offset = 0;
                    var bytesRead = 0;
                    do
                    {
                        bytesRead = (int)reader.GetBytes(ordinal, offset, result, offset, 1000);
                        offset += bytesRead;
                    } while (bytesRead == 1000);
                }
            }

            return result;
        }

        private static void AddParameter(DbCommand command, string parameterName, DbType dbType, int size, object value)
        {
            var parameter = command.CreateParameter();
            parameter.ParameterName = parameterName;
            parameter.DbType = dbType;
            parameter.Size = size;
            parameter.Value = value;

            command.Parameters.Add(parameter);
        }

        private static T Execute<T>(Func<DbCommand, T> executor)
        {
            TransactionScope transaction = null;
            DbConnection connection = null;
            DbCommand command = null;

            try
            {
                transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions()
                {
                    IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
                });

                connection = SqlClientFactory.Instance.CreateConnection();
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

                command = connection.CreateCommand();
                command.CommandType = CommandType.Text;

                connection.Open();

                var result = executor(command);

                transaction.Complete();

                return result;
            }
            finally
            {
                if (transaction != null) transaction.Dispose();
                if (connection != null) connection.Dispose();
                if (command != null) command.Dispose();
            }
        }
    }
}